﻿Imports System.Data.SqlClient
Imports System.Data



''' <summary>
''' Class provides all methods to interact with DB
''' </summary>
''' <remarks></remarks>
Public Class DataProvider
    Protected Shared strConnect As String   ' "Server=THEVO-PC\SQLEXPRESS;Database=TIMEKEEPER;User Id=yubiuser;Password=yubipass;"
    Protected Shared sqlConnect As SqlConnection


    Protected Shared Sub ConfigServer(ByVal strConnect As String)

        'strConnect = "Server=" & serverName & "\SQLEXPRESS2008;Database=TIMEKEEPER;User Id=satuser;Password=satpass;"
        DataProvider.strConnect = strConnect
        sqlConnect = New SqlConnection()
        sqlConnect.ConnectionString = strConnect
    End Sub


    Protected Shared Sub Open()

        sqlConnect = New SqlConnection()

        sqlConnect.ConnectionString = strConnect

        sqlConnect.Open()

    End Sub

    Protected Shared Function Update(ByVal storedProcedureName As String) As Integer

        'Try
        sqlConnect.Open()
        Dim sqlCom As SqlCommand
        sqlCom = New SqlCommand()
        sqlCom.Connection = sqlConnect
        sqlCom.CommandType = CommandType.StoredProcedure
        sqlCom.CommandText = storedProcedureName
        Dim result As Integer = sqlCom.ExecuteNonQuery()
        Close()
        Return result
        'Catch ex As ApplicationException
        '    System.Console.WriteLine(ex.Message)
        '    MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Message")
        'Catch ex As Exception
        '    System.Console.WriteLine(ex.Message)
        '    MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Message")
        'End Try
        'Return 0

    End Function


    Protected Shared Function Update(ByVal storedProcedureName As String, ByVal paramName As String(), ByVal paramValue As Object()) As Integer

        Try
            sqlConnect.Open()
            Dim sqlCom As SqlCommand
            sqlCom = New SqlCommand()
            sqlCom.Connection = sqlConnect
            sqlCom.CommandType = CommandType.StoredProcedure
            sqlCom.CommandText = storedProcedureName
            AddSQLParameter(sqlCom, paramName, paramValue)
            Dim result As Integer = sqlCom.ExecuteNonQuery()
            Close()
            Return result
            'Catch ex As ApplicationException
            '    System.Console.WriteLine(ex.Message)
            '    MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Message")
        Catch ex As Exception
            Close()
            System.Console.WriteLine(ex.Message)
            'MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Message")
        End Try
        Return 0
    End Function


    Protected Shared Function Query(ByVal storedProcedureName As String) As DataTable
        Dim dt As DataTable = New DataTable()
        'Try
        sqlConnect.Open()
        Dim sqlCom As SqlCommand = New SqlCommand()
        sqlCom.Connection = sqlConnect
        sqlCom.CommandType = CommandType.StoredProcedure
        sqlCom.CommandText = storedProcedureName
        Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(sqlCom)
        dataAdapter.Fill(dt)
        Close()
        Return dt
        'Catch ex As Exception
        '    MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Message")
        'End Try
        'Return dt
    End Function

    Protected Shared Function Query(ByVal storedProcedureName As String, ByVal paramName As String(), ByVal paramValue As Object()) As DataTable
        Dim dt As DataTable = New DataTable()
        'Try
        sqlConnect.Open()
        Dim sqlCommand As SqlCommand = New SqlCommand()
        sqlCommand.Connection = sqlConnect
        sqlCommand.CommandType = CommandType.StoredProcedure
        sqlCommand.CommandText = storedProcedureName
        AddSQLParameter(sqlCommand, paramName, paramValue)
        Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(sqlCommand)
        dataAdapter.Fill(dt)
        Close()
        Return dt
        'Catch ex As Exception
        '    MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Message")
        'End Try
        'Return dt
    End Function

    'this sub closes connection to the DB server
    Protected Shared Sub Close()
        If (sqlConnect Is Nothing) <> True Then
            sqlConnect.Close()
        End If
    End Sub

    Private Shared Sub AddSQLParameter(ByRef sqlCommand As SqlCommand, ByVal paramName As String(), ByVal paramValue As Object())

        If (paramName Is Nothing) <> True Then
            Dim param(paramName.Length - 1) As SqlParameter
            For i As Integer = 0 To paramName.Length - 1
                param(i) = New SqlParameter(paramName(i), paramValue(i))
                '  Console.WriteLine(paramValue(i))
            Next
            sqlCommand.Parameters.AddRange(param)
        End If

    End Sub

    Protected Shared Function NonQueryReturn(ByVal storedProcedureName As String, ByVal paramName As String(), ByVal paramValue As Object()) As Integer
        
        'Try
        sqlConnect.Open()
        Dim sqlCom As SqlCommand
        sqlCom = New SqlCommand()
        sqlCom.Connection = sqlConnect
        sqlCom.CommandType = CommandType.StoredProcedure
        sqlCom.CommandText = storedProcedureName
        AddSQLParameter(sqlCom, paramName, paramValue)

        ' add parameter get return value
        Dim pReturn As SqlParameter = sqlCom.Parameters.Add("@return", SqlDbType.Int)

        pReturn.Direction = ParameterDirection.ReturnValue
        sqlCom.ExecuteNonQuery()
        Dim result As Integer = pReturn.Value
        Close()
        Return result
        'Catch ex As ApplicationException
        '    System.Console.WriteLine(ex.Message)
        '    MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Message")
        'Catch ex As Exception
        '    System.Console.WriteLine(ex.Message)
        '    MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Message")
        'End Try
        'Return 0

    End Function

End Class
